﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace SqlServerDriver
{
	public class SqlTableSchema
	{
		String tableName = "";
		List<DbView.FieldInfo> fields;

		public SqlTableSchema()
		{
			this.fields = new List<DbView.FieldInfo>();
		}

		public int Count { get { return this.fields.Count; } }

		public DbView.FieldInfo this[int i]
		{
			get { return this.fields[i]; }
		}

		public DbView.FieldInfo this[String name]
		{
			get
			{
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.Name.ToUpper().CompareTo(name.ToUpper()) == 0)
						return fi;
				return null;
			}
		}

		public DbView.FieldInfo IdentityField
		{
			get
			{
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsAuto)
						return fi;
				return null;
			}
		}

		public DbView.FieldInfo[] PKeyFields
		{
			get
			{
				int keyCount = 0;
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsPKey)
						++keyCount;
				DbView.FieldInfo[] pkeyList = new DbView.FieldInfo[keyCount];
				keyCount = 0;
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsPKey)
						pkeyList[keyCount++] = fi;
				return pkeyList;
			}
		}

		// very tied to the schema info
		public void Populate(DataTable schemaTable,
							String nameCol, String typeCol, String lenCol, String decimalCol, String pkeyCol, String autoCol,
							String fkeyCol, String indexCol, String calcCol, String defCol, String definitionCol, String nullCol)
		{
			this.tableName = schemaTable.TableName;

			// find 
			foreach (DataRow dr in schemaTable.Rows)
			{
				String name = this.DataFromCell(dr, nameCol);
				String sqlType = this.DataFromCell(dr, typeCol);
				String leng = this.DataFromCell(dr, lenCol);
				String dp = this.DataFromCell(dr, decimalCol);
				String pk = this.DataFromCell(dr, pkeyCol);
				String fk = this.DataFromCell(dr, fkeyCol);
				String auto = this.DataFromCell(dr, autoCol);
				String index = this.DataFromCell(dr, indexCol);
				String calc = this.DataFromCell(dr, calcCol);
				String nulls = this.DataFromCell(dr, nullCol); nulls = (nulls.Length == 0) ? "Yes" : nulls; // Special. For null column blank means Yes
				String def = this.DataFromCell(dr, defCol);
				String definition = this.DataFromCell(dr, definitionCol);
				//
				int len = (leng.ToUpper() == "MAX") ? -1 :
						  (leng.Length == 0) ? 0 : // 2000 driver
						   Convert.ToInt32(leng);
				DbView.FieldInfo fi = new DbView.FieldInfo(name, sqlType, len);
				fi.IsAuto = this.TfFromData(auto); // should resolve auto inc info to Y if present
				this.ParseAutoData(fi, auto);
				fi.IsPKey = this.TfFromData(pk);
				fi.IsFKey = this.TfFromData(fk);
				fi.IsIndexed = this.TfFromData(index);
				if (sqlType.ToUpper() == "NCHAR" || sqlType.ToUpper() == "NVARCHAR")
					fi.IsUnicode = true;
				fi.IsCalc = this.TfFromData(calc);
				fi.AllowsNulls = this.TfFromData(nulls);
				fi.HasDefault = this.TfFromData(def);
				fi.ConstraintDefinition = definition;
				fi.Dp = (dp.Length == 0) ? 0 : Convert.ToInt32(dp);
				this.fields.Add(fi);
			}
		}

		String DataFromCell(DataRow dr, String columnName)
		{
			if (!dr.Table.Columns.Contains(columnName)) return "";
			return dr[columnName].ToString();
		}

		bool TfFromData(String data)
		{
			if (data.Length == 0) return false;  // empty means no
			char c = data[0];
			if (c == 'Y' || c == 'y' || c == 'T' || c == 't') return true;
			if (c == '1') return true;
			if (c == '0') return true;
			// Unless N or No then true
			if ((c == 'N' || c == 'n') && data.Length <= 2) return false;
			// handle other cases as they arise
			return true;
		}
		// check for (m,m)
		void ParseAutoData(DbView.FieldInfo fi, String autoData)
		{
			SimpleRegex sr = new SimpleRegex(@"\(\s*(\d+)\s*,\s*(\d+)\s*\)");
			if (sr.MatchText(autoData))
			{
				fi.AutoSeed = Convert.ToInt32(sr[1]);
				fi.AutoInc = Convert.ToInt32(sr[2]);
			}
		}
	}

	public class SqlScriptingHelper
	{
		String sourceTable;
		readonly SqlTableSchema schema;

		public SqlTableSchema Schema { get { return this.schema; } }
		public SqlScriptingHelper(String sourceTable, SqlTableSchema schema)
		{
			this.sourceTable = sourceTable;
			this.schema = schema;
		}

		public void SetFieldFilter(String[] fieldList) { this.fieldFilter = fieldList; }

		public String ScriptKeyCompare() { return this.ScriptKeyCompare(this.sourceTable); }
		public String ScriptKeyCompare(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					if (script.Length > 0)
						script += " and";
					script += String.Format(" {1}.{0}={2}.{0}", Helper.SafeSqlField(fi.Name), Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable));
				}
			}
			return script;
		}

		public String ScriptKeyList() { return this.ScriptKeyList(this.sourceTable); }
		public String ScriptKeyList(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					if (script.Length > 0)
						script += ",";
					if (targetTable.Length > 0)
						script += String.Format(" {1}.{0}", Helper.SafeSqlField(fi.Name), Helper.SafeTableName(targetTable));
					else
						script += String.Format("{0}", Helper.SafeSqlField(fi.Name));
				}
			}
			return script;
		}

		public String ScriptUpdate() { return this.ScriptUpdate(this.sourceTable); }
		public String ScriptUpdate(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsPKey && !fi.IsAuto && !fi.IsCalc && this.InFilter(fi))
				{
					if (script.Length > 0)
						script += ",\n";
					script += String.Format(" {1}.{0}={2}.{0}", Helper.SafeSqlField(fi.Name), Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable));
				}
			}
			return script;
		}

		public enum ScriptIdentOption { Before, After, AfterWithReseed };

		// Leave Id field blank if not reseeding and you don't know it
		public static String ScriptEnableIdentityModification(ScriptIdentOption option, String targetTable, String identityField)
		{
			if (option == ScriptIdentOption.AfterWithReseed && (identityField == null || identityField.Length == 0))
				throw new ArgumentException("Null or zero length Identity field parameter not allowed with option: AfterWithReseed");

			String afterWithReseedTemplate = @"
set IDENTITY_INSERT {0} OFF 
declare @reseed int
select @reseed = max({1}) from {0}
DBCC CHECKIDENT ('{2}', RESEED, @reseed)
";
			switch (option)
			{
				case ScriptIdentOption.Before: return String.Format(" set IDENTITY_INSERT {0} ON\n", Helper.SafeTableName(targetTable));
				case ScriptIdentOption.After: return String.Format("set IDENTITY_INSERT {0} OFF\n", Helper.SafeTableName(targetTable));
				case ScriptIdentOption.AfterWithReseed:
					return String.Format(afterWithReseedTemplate, Helper.SafeTableName(targetTable), Helper.SafeSqlField(identityField), targetTable);
			}
			return ""; // or it won't compile.
		}

		public String ScriptEnableIdentityModification(ScriptIdentOption option, String targetTable)
		{
			return ScriptEnableIdentityModification(option, targetTable, this.schema.IdentityField.Name);
		}

		public String ScriptEnableIdentityModification(ScriptIdentOption option)
		{
			if (this.schema.IdentityField == null) return ""; // n.a.
			return ScriptEnableIdentityModification(option, this.sourceTable);
		}

		private bool InFilter(DbView.FieldInfo fi)
		{
			// ok better with a Dictionary, but I don't think speed is of the essence here.
			if (this.fieldFilter == null) return true; // filter not active
			foreach (String s in this.fieldFilter)
				if (s.ToUpper() == fi.Name.ToUpper()) return true;
			return false;
		}

		public String ScriptCheckSumCompare(bool forEquality) { return this.ScriptCheckSumCompare(this.sourceTable, forEquality); }
		public String ScriptCheckSumCompare(String targetTable, bool forEquality)
		{
			String eq = (forEquality) ? "=" : "!=";
			String[] src = new String[this.schema.Count];
			String[] tgt = new String[this.schema.Count];
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				src[idx] = String.Format("{0}.{1}", Helper.SafeTableName(this.sourceTable), Helper.SafeSqlField(fi.Name));
				tgt[idx] = String.Format("{0}.{1}", Helper.SafeTableName(targetTable), Helper.SafeSqlField(fi.Name));
			}
			return String.Format("CHECKSUM({0}){2}\nCHECKSUM({1})", String.Join(",", src), String.Join(",", tgt), eq);
		}

		public String ScriptCompareFields(String srcAlias, string tgtAlias)
		{
			int keyFieldCount = this.KeyFieldCount();
			String[] src = new String[this.schema.Count - keyFieldCount];
			int idx = 0;
			for (int idx2 = 0; idx2 < this.schema.Count; ++idx2)
			{
				DbView.FieldInfo fi = this.schema[idx2];
				if (!fi.IsPKey)
				{
//					string template = "	{0}.{1} as n{1}, {3}.{1} as o{1}, case when NOT(ISNULL({0}.{1},{4}) = ISNULL({3}.{1},{4})) then 'N' else '' end as d{1}";
					string template = (fi.Type == "N") ? "		{0}.{1} as n{1}, case when NOT(ABS(ISNULL({0}.{1},{4}) - ISNULL({3}.{1},{4})) < .1) then cast({3}.{1} as varchar(50)) else '' end as o{1}"
														:"		{0}.{1} as n{1}, case when NOT(ISNULL({0}.{1},{4}) = ISNULL({3}.{1},{4})) then {3}.{1} else '' end as o{1}";
					string nullToken = MakeNullProxy(fi);
					src[idx] = string.Format(template, srcAlias, fi.Name, Helper.SafeSqlField(fi.Name), tgtAlias, nullToken);
					++idx;
				}
			}
			return String.Join(",\n", src)+"\n";
		}

		public String ScriptRowNotEqual(String srcAlias, string tgtAlias)
		{
			int keyFieldCount = this.KeyFieldCount();
			String[] src = new String[this.schema.Count - keyFieldCount];
			int idx = 0;
			for (int idx2 = 0; idx2 < this.schema.Count; ++idx2)
			{
				DbView.FieldInfo fi = this.schema[idx2];
				if (!fi.IsPKey)
				{
					string template = (fi.Type == "N") ? "\n or NOT(ABS(ISNULL({0}.{1},{3}) - ISNULL({2}.{1},{3})) < .1)"
														: "\n or NOT(ISNULL({0}.{1},{3}) = ISNULL({2}.{1},{3}))";
					string nullToken = MakeNullProxy(fi);
					src[idx] = string.Format(template, srcAlias, Helper.SafeSqlField(fi.Name), tgtAlias, nullToken);
					++idx;
				}
			}
			return String.Join("", src)+"\n";
		}

		private int KeyFieldCount()
		{
			int kc = 0;
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey) ++kc;
			}
			return kc;
		}

		private String MakeNullProxy(DbView.FieldInfo field)
		{
			return (field.Type == "C") ? "'`'" : // choose an unlikely character.
			(field.Type == "I") ? "-8989" :  // random very unusual value.
			(field.Type == "J") ? "-89898989" :  // random very unusual value (bigint)
			(field.Type == "N") ? "-8989.9898" :  // random very unusual value.
			(field.Type == "D") ? "'19700102'"
							   : "'0'"; // this should suit all
		}



		public String ScriptAllFields()
		{
			String[] src = new String[this.schema.Count];
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (this.InFilter(fi)) // not expecting this method to be called with an active filter
					src[idx] = String.Format("{0}", Helper.SafeSqlField(fi.Name));
			}
			return String.Join(",", src);
		}

		public String ScriptAllButCalcFields()
		{
			List<String> src = new List<string>();
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsCalc && this.InFilter(fi))
					src.Add(String.Format("{0}", fi.SafeName));
			}
			return String.Join(",", src.ToArray());
		}

		public bool TablesCompare(SqlTableSchema compareSchema)
		{
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (compareSchema[fi.Name] == null)
					return false;
			}
			return true;
		}

		public String ScriptRowsetRefresh(DbView.ConnectionInfo sourceConnection)
		{
			String script = "/*  Script to refresh a table (0) */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			this.sourceTable = String.Format("#{0}", this.sourceTable);

			script += String.Format(@"
if object_id('tempdb..{0}') is not null drop table {0}
select dt.* into {0} from OPENROWSET('SQLNCLI','{1}';'{2}';'{3}', {4}.dbo.{5}) AS dt
            ", this.sourceTable, sourceConnection.Server, sourceConnection.UID, sourceConnection.PWD, sourceConnection.Database, this.sourceTable);

			script += String.Format(@"
delete from {0} where not exists
       (select * from {1} where {2})
            ", Helper.SafeTableName(this.sourceTable), Helper.SafeTableName(this.sourceTable), this.ScriptKeyCompare());
			/*
update BANK set 
	BANK_NAME = BANK_20081017_103911.BANK_NAME
 from BANK_20081017_103911
where BANK.BANK_ID = BANK_20081017_103911.BANK_ID
 and CHECKSUM(BANK.BANK_ID) <> CHECKSUM(BANK_20081017_103911.BANK_ID)
			*/
			script += String.Format(@"
update {0} set {2}
  from {1}
 where {3} 
   and {4}
            ", Helper.SafeTableName(this.sourceTable), Helper.SafeTableName(this.sourceTable),
						this.ScriptUpdate(),
					   this.ScriptKeyCompare(),
					   this.ScriptCheckSumCompare(false));

			DbView.FieldInfo idField = this.schema.IdentityField;
			if (idField != null)
			{
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, this.sourceTable, "");
			}
			script += String.Format(@"
insert into {0} ({2})
select {2} 
  from {1} 
 where not exists (select * from {0} where {3}) 
            ", Helper.SafeTableName(this.sourceTable), Helper.SafeTableName(this.sourceTable), this.ScriptAllButCalcFields(), this.ScriptKeyCompare());

			if (idField != null)
			{
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, this.sourceTable, idField.Name);
			}
			return script;
		}

		public String ScriptTableRefresh()
		{
			String script = "/*  Script to refresh a table (0) */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
            delete from {0} where not exists
                   (select * from {1} where {2})
            ", Helper.SafeTableName(this.sourceTable), Helper.SafeTableName(this.sourceTable), this.ScriptKeyCompare());

			script += String.Format(@"
            update {0} set {2}
              from {1}
             where {3} 
               and {4}
            ", Helper.SafeTableName(this.sourceTable), Helper.SafeTableName(this.sourceTable), this.ScriptUpdate(),
					   this.ScriptKeyCompare(),
					   this.ScriptCheckSumCompare(false));

			DbView.FieldInfo idField = this.schema.IdentityField;
			if (idField != null)
			{
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.Before, this.sourceTable, "");
			}
			script += String.Format(@"
            insert into {0} ({2})
            select {2} 
              from {1} 
             where not exists (select * from {0} where {3}) 
            ", Helper.SafeTableName(this.sourceTable), Helper.SafeTableName(this.sourceTable), this.ScriptAllButCalcFields(), this.ScriptKeyCompare());

			if (idField != null)
			{
				// identidy insert
				script += SqlScriptingHelper.ScriptEnableIdentityModification(SqlScriptingHelper.ScriptIdentOption.AfterWithReseed, this.sourceTable, idField.Name);
			}
			return script;
		}

		// for import export set applicable filter for fields to script
		String[] fieldFilter = null;
	}
}
